clear all
set more off, perm
set maxvar 120000
set varabbrev off
* ---------------------------------------------- *
global dir 	"`1'"
global Data 	$dir/Data
global Tables 	$dir/Tables
global Figures 	$dir/Figures
global Work 	$dir/Work
global Temp 	$dir/Temp
global Pseudo	$dir/Work/Temp
* ---------------------------------------------- *
* Get QUARTERLY IBES forecasts
import sas $Data/IBES/statsumu_epsus.sas7bdat, clear case(lower)
rename *, lower
compress
keep if (index(measure,"EPS")>0  & index(curcode,"USD")>0) 
drop if index(fpi,"A") | index(fpi,"B") | index(fpi,"C") | index(fpi,"D")
destring fpi, replace
keep if (fpi>=6 & fpi<=9)

keep statpers ticker fpi medest fpedats
* *************************************
merge m:1 ticker statpers using $Work/link_ibes_crsp2024, keepusing(comnam cname permco permno ticker statpers) 
keep if _merge==3  
drop _merge
* ****************************************
* interpolate the data
egen id=group(permco permno statpers)
gen fpdatem=mofd(fpedats)
format fpdatem %tm
* **** 
drop fpedats fpi
tsset id fpdatem
count
tsfill
count
* ****
sort id fpdatem

foreach var of varlist statpers permco permno { 
	replace `var'=L.`var' if missing(`var') 
}
foreach var of varlist ticker cname comnam {
	replace `var'=`var'[_n-1] if missing(`var')   & id==id[_n-1]
}
tsset id fpdatem
by id: ipolate medest fpdatem, gen(imedest)
gen fpi=fpdatem-mofd(statpers)
keep if fpi>0 & fpi<=12 
drop fpdatem id medest
rename imedest medest
reshape wide medest, i(statpers cname comnam ticker permco permno) j(fpi)  
* *************************************
* get CRSP split-adjustment, shares outstanding and mkt cap data -- data generated by EBR_monthly.do
merge 1:1 permco permno statpers using $Work/dsf, keepusing(permco permno statpers cfacshr)
keep if _merge==3
drop _merge

foreach var of varlist med* {
	replace  `var'=`var'/cfacshr 
}
drop cfacshr
tempfile FE_MO
save "`FE_MO'", replace
/* ********************************************************************* */
* [1] LOAD QUARTERLY COMPUSTAT
* wrds library: /wrdslin/comp/sasdata/d_na/
import sas using $Data/Compustat/fundq.sas7bdat, clear 
rename *, lower
keep if index(curcdq, "USD")>0
* *********************************
keep conm* gvkey datadate fyr fyearq fqtr cshoq xidoq spiq nrtxtq ibq niq
tempfile tmp
save "`tmp'", replace
/* ********************************************************************* */
import sas using $Data/CRSP/ccmxpf_linktable.sas7bdat, case(lower) clear
keep if  index(linktype,"L") 
keep if (index(linkprim,"P") |  index(linkprim,"C"))
format linkdt linkenddt %td

joinby gvkey using "`tmp'"
keep if datadate>=linkdt & datadate<=linkenddt
rename lpermno permno
rename lpermco permco
drop linkdt linkenddt
* ******************************
* will need to tsset the data to compute moving average of earnings
drop if fqtr==.
* get rid of duplicate obs 
*linktype: "LD"  Duplicate link to a security. Another GVKEY/IID is a better link to that CRSP 
duplicates tag permco permno datadate, gen(mf)
tabulate mf

drop if mf>0 & index(linktype,"LD")>0
drop mf
* ****** 
*linkprim: Primary issue marker for the link.
duplicates tag permco permno datadate, gen(mf)
tabulate mf
drop if mf>0 & index(linkprim,"P")==0
drop mf
/* ********************************************************************* */
* compute street earnings
egen tmp=rowtotal(xidoq spiq nrtxtq)
generate street=niq - tmp
replace  street=ibq		if street==.
replace  street=niq  		if street==.
drop xidoq spiq nrtxtq niq ibq tmp 
drop usedflag linkprim liid linktype
format datadate %td

* may have multiple records when firms change fiscal years
duplicates tag permno datadate, gen(dup)
sort permno datadate
* get rid of duplicates that don't matter for the street data
drop if dup==1 & street==street[_n-1] & permno==permno[_n-1] & datadate==datadate[_n-1] 
drop if dup==1 & street==.
drop dup
duplicates tag permno datadate, gen(dup)
tabulate dup

egen id=group(permno fyr)

bys permno datadate: egen min=min(id)
drop if dup==1 & id>min
drop dup

duplicates tag permno datadate, gen(dup)
tabulate dup
drop dup
gen statpers=datadate
drop if year(datadate)==2024

merge 1:1 permco permno statpers using $Work/dsf, keepusing(permco permno statpers shr* cfacshr)
keep if _merge==3
drop _merge

generate street_share=street/cshoq
replace  street_share=street/(shrout/1000) if cshoq==.
generate street_share_adj=street_share/cfacshr  

generate datem=mofd(datadate)
format   datem %tm

keep permno permco datem street street_share street_share_adj
count
tsset permno datem
tsfill
count
by permno: ipolate street		datem, gen(istreet)
by permno: ipolate street_share		datem, gen(istreet_share)
by permno: ipolate street_share_adj 	datem, gen(istreet_share_adj)

foreach var of varlist street* {
	replace `var'=i`var'	if `var'==.
}
replace permco=L.permco 	if permco==.

drop istreet* 

rename datem fpdatem 

keep permco permno fpdatem street*
save $Work/EPS_MO, replace
/* ********************************************************************* */
use  "`FE_MO'", clear
reshape long medest , i(permco permno statpers comnam ticker) j(h)
gen fpdatem=mofd(statpers)+h
format fpdatem %tm

merge m:1 permco permno fpdatem using $Work/EPS_MO
drop if _merge==2
drop _merge fpdatem

reshape wide medest street street_share street_share_adj, i(permco permno statpers comnam ticker) j(h)
order *, sequential
order permco permno statpers comnam ticker medest* street_share_adj* street_share*  street1-street12
save $Work/FE_MO, replace
/* ********************************************************************* */
